package com.sinodata.bsm.cicp.collector.sqlserver;

import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.log4j.Logger;

import com.sinodata.bsm.cicp.collector.AbstractJDBCCollector;
import com.sinodata.bsm.cicp.exception.CollectorException;
import com.sinodata.bsm.cicp.util.DBConnectionUtil;
import com.sinodata.bsm.common.vo.ResPropertyValue;

public class TableCollector extends AbstractJDBCCollector {
    private static Logger logger = Logger.getLogger(TableCollector.class.getName());

    @Override
    public List<ResPropertyValue> collect(String ip) throws CollectorException {
        List<ResPropertyValue> values = new ArrayList<ResPropertyValue>();
        try {
            String[] propMetas = getPropMeta();
            String serverName = this.getParamMap().get("ServerName");
            String dbName = this.getParamMap().get("DBName");

            String sql = "select o.name, u.name" + " from " + dbName + ".dbo.sysobjects o, " + dbName + ".dbo.sysusers u" + " where o.xtype = 'U' and o.uid = u.uid " + " order by o.name";
            String[] rs = this.getStrings(serverName, sql, ip, dbName);

            ResPropertyValue propertyValueX = new ResPropertyValue();
            ResPropertyValue.PropValue[] propValues = new ResPropertyValue.PropValue[propMetas.length];
            for (int i = 0; i < propMetas.length; i++) {
                propValues[i] = ResPropertyValue.createPropValue(rs[i], propMetas[i]);
            }

            propertyValueX.setValues(propValues);
            values.add(propertyValueX);
        } catch (Exception ex) {
            logger.error("failed to collect sqlserver tablecollector", ex);
            throw new CollectorException(ex);
        }
        return values;
    }

    private String[] getStrings(String serverName, String sql, String ip, String databaseName) throws CollectorException {
        String[] propValArr = new String[5];

        CallableStatement cstmt = null;
        Statement stmt = null;
        ResultSet rst = null;
        Connection conn = null;
        try {
            conn = DBConnectionUtil.getInstance().getConnection("sqlserver", ip, serverName);
            if (conn == null) {
                return null;
            }
            stmt = conn.createStatement();
            stmt.setQueryTimeout(10);
            rst = stmt.executeQuery(sql);
            List<String> tableList = new ArrayList<String>();
            if (rst != null) {
                while (rst.next()) {
                    String tableName = databaseName + "." + rst.getString(2) + "." + rst.getString(1);
                    tableList.add(tableName);
                }
            }

            Iterator it = tableList.iterator();
            String tableName = "";
            while (it.hasNext()) {
                tableName = it.next().toString();
                int rowCount = 0;
                int reservedSize = 0;
                int dataSize = 0;
                int indexSize = 0;
                int unusedSize = 0;

                sql = "{call " + databaseName + ".dbo.sp_spaceused(?)}";
                cstmt = conn.prepareCall(sql);
                cstmt.setString(1, tableName);
                cstmt.setQueryTimeout(10);
                rst = cstmt.executeQuery();
                if (rst != null) {
                    while (rst.next()) {
                        rowCount = Integer.parseInt(cutUnit(rst.getString(2)));
                        reservedSize = Integer.parseInt(cutUnit(rst.getString(3)));
                        dataSize = Integer.parseInt(cutUnit(rst.getString(4)));
                        indexSize = Integer.parseInt(cutUnit(rst.getString(5)));
                        unusedSize = Integer.parseInt(cutUnit(rst.getString(6)));
                    }
                }
                propValArr[0] = rowCount + "";
                propValArr[1] = reservedSize + "";
                propValArr[2] = dataSize + "";
                propValArr[3] = indexSize + "";
                propValArr[4] = unusedSize + "";
            }
            return propValArr;
        } catch (SQLException ex) {
            logger.error("when query SQL Server database " + serverName + "," + databaseName, ex);
            throw new CollectorException(ex);
        } catch (IOException ioe) {
            logger.error("when connect SQL Server " + serverName + "," + databaseName, ioe);
            throw new CollectorException(ioe);
        } finally {
            closeResultSet(rst);
            closeStatement(stmt);
            closeStatement(cstmt);
            DBConnectionUtil.getInstance().returnConnection(conn);
        }
    }

    @Override
    public String[] getParamMeta() {
        return new String[] { "ServerName", "DBName" };
    }

    @Override
    public String[] getPropMeta() {
        return new String[] { "DataRows", "TotalReservedSpaceSize", "DataUsedSpaceSize", "IndexUsedSpaceSize", "UnusedSpaceSize" };
    }

    private static void closeResultSet(ResultSet rst) throws CollectorException {
        try {
            if (rst != null)
                rst.close();
        } catch (SQLException ex) {
            logger.error("failed to close result set", ex);
            throw new CollectorException(ex);
        }
    }

    private static void closeStatement(Statement sta) throws CollectorException {
        try {
            if (sta != null)
                sta.close();
        } catch (SQLException ex) {
            logger.error("failed to close statement", ex);
            throw new CollectorException(ex);
        }
    }

    private String cutUnit(String value) {
        return value.substring(0, value.length() - 3).trim();
    }

}
